package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import Conexao.ConnectionManager;
import Modelo.Bibliotecario;
import Modelo.Cliente;
import Modelo.Livro;



public class ClienteDaoImp implements ClienteDAO{
	
	private final String INSERT = "insert into cliente (nome, cpf, sexo, nascimento, telefone, login, senha) values (?,?,?,?,?,?,?)";
	private final String UPDATE = "update cliente set nome = ?, cpf = ?, sexo = ?, nascimento = ?, telefone = ?, where idCliente = ?";
	private final String DELETE = "delete from cliente where idCliente = ?";
	private final String ALL = "select * from cliente";
	private final String CPF = "select * from cliente where cpf = ?";
	private final String NOME = "select * from cliente where nome = ?";
	private final String LOGIN = "select * from cliente where login = ? and senha = ?";
	private final String ID = "select * from  cliente where idCliente = ?";
	
	private static Connection connection;
	
	public ClienteDaoImp() {
		openConnection();
	}
	
	public Cliente salvar(Cliente e){
		try {
			PreparedStatement ps = null;
			
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(INSERT);
			
			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getCpf());
			ps.setObject(3, e.getSexo());
			ps.setObject(4, e.getNascimento());
			ps.setObject(5, e.getTelefone());
			ps.setObject(6, e.getLogin());
			ps.setObject(7, e.getSenha());
			
			
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return e;
	}
	
	public void alterar(Cliente e) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(UPDATE);
						
			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getCpf());
			ps.setObject(3, e.getSexo());
			ps.setObject(4, e.getNascimento());
			ps.setObject(5, e.getTelefone());
			ps.setObject(6, e.getLogin());
			ps.setObject(7, e.getSenha());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}
	
	public void deletar(Cliente e) {
		try {
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);

			ps.setObject(1, e.getIdCliente());
			
			ps.executeUpdate();

		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
	}
	
	public List<Cliente> findAll() {
		List<Cliente> clientes = new ArrayList<Cliente>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(ALL);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Cliente cliente = new Cliente();
				cliente.setIdCliente(rs.getInt("idcliente"));
				cliente.setNome(rs.getString("nome"));
				cliente.setCpf(rs.getString("cpf"));
				cliente.setSexo(rs.getString("sexo"));
				cliente.setNascimento(rs.getDate("nascimento"));
				cliente.setTelefone(rs.getString("telefone"));
				clientes.add(cliente);
			}

		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		return clientes;
	}
	
	public Cliente buscarClientePorCpf(String cpf) {
		List<Cliente> clientes = new ArrayList<Cliente>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(CPF);
			ps.setObject(1, cpf);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Cliente cliente = new Cliente();
				cliente.setIdCliente(rs.getInt("idcliente"));
				cliente.setNome(rs.getString("nome"));
				cliente.setCpf(rs.getString("cpf"));
				cliente.setSexo(rs.getString("sexo"));
				cliente.setNascimento(rs.getDate("nascimento"));
				cliente.setTelefone(rs.getString("telefone"));
				clientes.add(cliente);
			}

		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (clientes.isEmpty()) {
			return null;
		} else {
			return clientes.get(0);
		}
	}
	
	public Cliente buscarClientePorNome(String nome) {
		List<Cliente> clientes = new ArrayList<Cliente>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(NOME);
			ps.setObject(1, nome);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Cliente cliente = new Cliente();
				cliente.setIdCliente(rs.getInt("idcliente"));
				cliente.setNome(rs.getString("nome"));
				cliente.setCpf(rs.getString("cpf"));
				cliente.setSexo(rs.getString("sexo"));
				cliente.setNascimento(rs.getDate("nascimento"));
				cliente.setTelefone(rs.getString("telefone"));
				clientes.add(cliente);
			}

		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (clientes.isEmpty()) {
			return null;
		} else {
			return clientes.get(0);
		}
	}
	
	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
	}

	@Override
	public void openConnection() {
		if ( connection == null) {
			connection = ConnectionManager.getInstance().getConnection();
		}
		
		if ( isConnectionClose() ) {
			connection = ConnectionManager.getInstance().getConnection();
		}		
	}

	@Override
	public void closeConnection() {
		if ( connection != null && !this.isConnectionClose()) {
			ConnectionManager.getInstance().closeConnection(connection);
		}
		
	}

	@Override
	public boolean login(String login, String senha) {
		List<Cliente> clientes = new ArrayList<Cliente>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(LOGIN);
			ps.setObject(1, login);
			ps.setObject(2, senha);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Cliente cliente = new Cliente();
				cliente.setLogin(rs.getString("login"));
				cliente.setSenha(rs.getString("senha"));
				clientes.add(cliente);
			}

		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (clientes.isEmpty()) {
			return false;
		} else {
			return true;
		}
	}
	
	public Cliente buscarClientePorId(int idCliente) {
		List<Cliente> clientes = new ArrayList<Cliente>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(ID);
			ps.setObject(1, idCliente);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Cliente cliente = new Cliente();
				cliente.setIdCliente(rs.getInt("idCliente"));
				cliente.setNome(rs.getString("nome"));
				cliente.setCpf(rs.getString("cpf"));
				cliente.setSexo(rs.getString("sexo"));
				cliente.setNascimento(rs.getDate("nascimento"));
				cliente.setTelefone(rs.getString("telefone"));
				clientes.add(cliente);
				
				
				clientes.add(cliente);
			}

		} catch (SQLException ex) {
			Logger.getLogger(ClienteDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (clientes.isEmpty()) {
			return null;
		} else {
			return clientes.get(0);
		}
	}


	
}

	

